import pandas as pd


def merge_excel_sheets(file_path):
    try:
        # 获取所有 sheet 的名称
        excel_file = pd.ExcelFile(file_path)
        sheet_names = excel_file.sheet_names  # 自动获取所有 sheet 名称

        # 定义需要的列
        id_column_options = ['准考证号', '考号']  # 准考证号的可能列名
        rank_column_options = ['名次', '总名', '总分名次', '序']  # 名次的可能列名

        dfs = []
        for sheet_name in sheet_names:
            df = pd.read_excel(excel_file, sheet_name=sheet_name)

            # 检查是否包含姓名列
            if '姓名' not in df.columns:
                print(f"Sheet '{sheet_name}' 缺少 '姓名' 列，已跳过。")
                continue  # 如果没有姓名列，跳过该 sheet

            # 找到准考证号和名次的实际列名
            id_column = next((col for col in id_column_options if col in df.columns), None)
            rank_column = next((col for col in rank_column_options if col in df.columns), None)

            if not id_column:
                print(f"Sheet '{sheet_name}' 缺少必要的列: 准考证号 或 考号，已跳过。")
                continue  # 如果没有 ID 列，跳过该 sheet

            # 选择需要的列，若某些列缺失则忽略
            selected_columns = ['姓名', id_column, rank_column, '班级', '总分']
            selected_columns = [col for col in selected_columns if col in df.columns]
            df = df[selected_columns].copy()

            # 创建唯一主键列：姓名 + 准考证号的前七位
            df['Unique_Key'] = df['姓名'].astype(str) + '_' + df[id_column].astype(str).str[2:7]

            # 添加sheet后缀
            dfs.append(df.add_suffix(f'_{sheet_name}'))
            dfs[-1].rename(columns={f'Unique_Key_{sheet_name}': 'Unique_Key'}, inplace=True)

        # 检查是否有可合并的数据
        if not dfs:
            print("没有可合并的 sheet 页。")
            return

        # 基于唯一主键列进行外连接并保留列后缀
        result = dfs[0]
        for i in range(1, len(dfs)):
            result = pd.merge(result, dfs[i], on='Unique_Key', how='outer')

        # 删除临时的 Unique_Key 列
        result.drop(columns=['Unique_Key'], inplace=True)

        # 保存结果到新的Excel文件
        result.to_excel('merged_output.xlsx', index=False)
        print("文件合并成功并保存为 'merged_output.xlsx'")

    except FileNotFoundError as e:
        print(f"文件未找到: {e}")
    except ValueError as e:
        print(f"数据处理错误: {e}")
    except Exception as e:
        print(f"出现错误: {e}")


# 示例使用
file_path = './file.xlsx'
merge_excel_sheets(file_path)
# 等待用户点击任意键再退出
input("按任意键退出...")

